package com.wsc.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.wsc.entity.Statistics;

public interface StatisticsRepository extends JpaRepository<Statistics, Integer>, JpaSpecificationExecutor<Statistics> {
	// IF (:readroomid is not null, readroomid = :readroomid , 1 = 1)
	@Query(value = "select * from t_statics where IF (:readroomid is not null, readroomid = :readroomid , 1 = 1) limit :page,:limit", nativeQuery = true)
	List<Statistics> getAll(@Param("readroomid") Integer readroomid, @Param("page") Integer page,
			@Param("limit") Integer limit);

	@Query(value = "select count(*) from t_statics where IF (:readroomid is not null, readroomid = :readroomid , 1 = 1)", nativeQuery = true)
	Integer getCount(@Param("readroomid") Integer readroomid);

	/***
	 * str_to_date(CONCAT_WS('-',year,month,day) 临时拼接列，转Date 
	 *IF(:intime is not null,str_to_date(CONCAT_WS('-',year,month,day), '%Y-%m-%d') >= :intime,1 = 1)
	 *and IF(:outtime is not null,str_to_date(CONCAT_WS('-',year,month,day),'%Y-%m-%d') <= :outtime,1 = 1) 
	 *注意小括号（成对出现）String类型可以和数据库Date比较时间
	 */
	@Query(value = "select r.name,sum(s.peoplenums) from t_statics s inner join t_readrooms r on s.readroomid = r.id where IF(:intime is not null,str_to_date(CONCAT_WS('-',year,month,day), '%Y-%m-%d') >= :intime,1 = 1) and IF(:outtime is not null,str_to_date(CONCAT_WS('-',year,month,day),'%Y-%m-%d') <= :outtime,1 = 1) group by s.readroomid", nativeQuery = true)
	List<Object[]> getChartData(@Param("intime") String intime, @Param("outtime") String outtime);

}
